﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace SIS.DataAccess
{
	/// <summary>
	/// Author: hieunv
	/// </summary>
	[Serializable]
	class Instructor : DataTable
	{
		public const string INSTRUCTOR_ID = "ID";
		public const string INSTRUCTOR_NAME = "name";
		public const string DEPARTMENT_NAME = "dept_name";
		public const string SALARY = "salary";

		public Instructor()
			: base("Instructor")
		{
			var id = new DataColumn(INSTRUCTOR_ID, typeof(string));
			id.AllowDBNull = false;
			id.Unique = true;

			var name = new DataColumn(INSTRUCTOR_NAME, typeof(string));
			name.AllowDBNull = false;

			var departmentName = new DataColumn(DEPARTMENT_NAME, typeof(string));
			departmentName.AllowDBNull = true;

			var salary = new DataColumn(SALARY, typeof(long));
			salary.AllowDBNull = true;

			this.Columns.Add(id);
			this.Columns.Add(name);
			this.Columns.Add(departmentName);
			this.Columns.Add(salary);

			this.PrimaryKey = new DataColumn[] { id };
		}

		public static DataTable GetInstructorIDs(string courseId)
		{
			var instructors = new DataTable();
			String query = "SELECT name FROM instructor WHERE ID IN (";
			query += "SELECT ID FROM teaches WHERE course_id = '" + courseId + "')";
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, query, null, instructors);
			return instructors;
		}
	}
}
